﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public class VolunteersDao
    {
        private readonly SqlConnection _sqlConnection;

        public VolunteersDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }

        public VolunteersDao(string sqlConnectionString)
        {
            _sqlConnection = new SqlConnection(sqlConnectionString);
        }

        #region Sql Commands
        private const string QueryStringSelectVolunteers = @"
                    SELECT V.ID, V.ContactID, C.FirstName + ' ' + C.SurName [ContactName], VolunteerPurpose, Details, 
                    CONVERT(VARCHAR(10), StartDate, 101) AS StartDate, CONVERT(VARCHAR(10), EndDate, 101) AS EndDate,
                    VolunteerNo, CASE(V.IsActive) WHEN 1 THEN 'Yes' ELSE 'No' END AS IsActive
                    FROM Volunteers V, Contact C
                    WHERE V.ContactID = C.ContactID AND PremisesID = @id";
        private const string QueryStringUpdateActive = @"
                    UPDATE Volunteers SET IsActive = @isActive WHERE ID = @id";
        private const string QueryStringInsertVolunteers = @"
                    INSERT INTO Volunteers(ContactID,VolunteerPurpose,Details,StartDate,EndDate,VolunteerNo,IsActive)
                    VALUES(@ContactID,@VolunteerPurpose,@Details,@StartDate,@EndDate,@VolunteerNo,@IsActive)";
        private const string QueryStringUpdateVolunteers = @"
                    UPDATE Volunteers SET ContactID=@ContactID, VolunteerPurpose=@VolunteerPurpose, Details=@Details,
                    StartDate=@StartDate, EndDate=@EndDate, VolunteerNo=@VolunteerNo
                    WHERE ID=@id";
        private const string QueryStringSelectVolunteersById = @"
                    SELECT ContactID, VolunteerPurpose, Details, IsActive, VolunteerNo,
                    CONVERT(VARCHAR(10), StartDate, 101) AS StartDate, CONVERT(VARCHAR(10), EndDate, 101) AS EndDate
                    FROM Volunteers
                    WHERE ID = @id";
        #endregion

        #region Methods
        /// <summary>
        /// Selects the volunteers by id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public Volunteers SelectVolunteersById(int id)
        {
            var param = new SqlParameter("@id", SqlDbType.Int) {Value = id};
            var dataRow = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectVolunteersById,
                                                   param).Tables[0].Rows[0];
            return new Volunteers
                       {
                           ContactId = (int) dataRow["ContactID"],
                           VolunteerPurpose = (string) dataRow["VolunteerPurpose"],
                           Details = (string) dataRow["Details"],
                           StartDate = (string) dataRow["StartDate"],
                           EndDate = (string) dataRow["EndDate"],
                           VolunteerNo = (string) dataRow["VolunteerNo"],
                           IsActive = (bool) dataRow["IsActive"]
                       };
        }
        public DataTable SelectVolunteersById1(int id)
        {
            var param = new SqlParameter("@id", SqlDbType.Int) {Value = 4};
            return SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectVolunteersById,
                                                   param).Tables[0];
        }

        /// <summary>
        /// Inserts the volunteers.
        /// </summary>
        /// <param name="volunteers">The volunteers.</param>
        /// <returns></returns>
        public int InsertVolunteers(Volunteers volunteers)
        {
            try
            {
                var param = new SqlParameter[7];
                param[0] = new SqlParameter("@ContactID", SqlDbType.Int){Value = volunteers.ContactId};
                param[1] = new SqlParameter("@VolunteerPurpose", SqlDbType.Text) { Value = volunteers.VolunteerPurpose };
                param[2] = new SqlParameter("@Details", SqlDbType.Text) { Value = volunteers.Details };
                param[3] = new SqlParameter("@StartDate", SqlDbType.VarChar) { Value = volunteers.StartDate };
                param[4] = new SqlParameter("@EndDate", SqlDbType.VarChar) { Value = volunteers.EndDate };
                param[5] = new SqlParameter("@VolunteerNo", SqlDbType.Float) { Value = volunteers.VolunteerNo };
                param[6] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = volunteers.IsActive };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, QueryStringInsertVolunteers, param);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                return -1;
            }
        }

        /// <summary>
        /// Updates the volunteers.
        /// </summary>
        /// <param name="volunteers">The volunteers.</param>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public int UpdateVolunteers(Volunteers volunteers, int id)
        {
            try
            {
                var param = new SqlParameter[7];
                param[0] = new SqlParameter("@ContactID", SqlDbType.Int) { Value = volunteers.ContactId };
                param[1] = new SqlParameter("@VolunteerPurpose", SqlDbType.Text) { Value = volunteers.VolunteerPurpose };
                param[2] = new SqlParameter("@Details", SqlDbType.Text) { Value = volunteers.Details };
                param[3] = new SqlParameter("@StartDate", SqlDbType.VarChar) { Value = volunteers.StartDate };
                param[4] = new SqlParameter("@EndDate", SqlDbType.VarChar) { Value = volunteers.EndDate };
                param[5] = new SqlParameter("@VolunteerNo", SqlDbType.Float) { Value = volunteers.VolunteerNo };
                param[6] = new SqlParameter("@id", SqlDbType.Int) { Value = id };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, QueryStringUpdateVolunteers, param);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                return -1;
            }
        }
        /// <summary>
        /// Selects the volunteers.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectVolunteers(int premisesId)
        {
            var param = new SqlParameter("@id", SqlDbType.Int) {Value = premisesId};
            var dataTable = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectVolunteers, param);
            return dataTable.Tables[0];
        }

        /// <summary>
        /// Marks the activated.
        /// </summary>
        /// <param name="isActive">if set to <c>true</c> [is active].</param>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public int MarkActivated(bool isActive, int id)
        {
            try
            {
                var parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter("@isActive", SqlDbType.Bit) { Value = isActive };
                parameters[1] = new SqlParameter("@id", SqlDbType.Int) { Value = id };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, QueryStringUpdateActive, parameters);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                return 0;
            }
            catch (ArgumentException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
        }
        #endregion
    }
}
